package cn.softeem.tushu.Dao.Impl;


import cn.softeem.tushu.Dao.MainDao;
import cn.softeem.tushu.po.Book;
import cn.softeem.tushu.po.Category;
import cn.softeem.tushu.po.User;
import cn.softeem.tushu.utils.PageBean;
import cn.softeem.tushu.utils.TxQueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MainDaoImpl implements MainDao {
    TxQueryRunner tqr =new TxQueryRunner();
    private static final Integer pageCount=8;
    @Override
    public  List<Category> getCategoryTwo() throws SQLException {
        return tqr.query("SELECT t1.*,t2.cname as pname from t_category t1,t_category t2 WHERE t1.pid=t2.cid ORDER BY orderBy",new BeanListHandler<Category>(Category.class));
    }

    @Override
    public PageBean<Book> getBookList(String x, int pageNow , String type) throws SQLException {
        PageBean<Book>bean=new PageBean<>();
        bean.setPageNow(pageNow);
        //查询总记录数
        String sql="select count(*) from t_book where "+type+"=?";
        bean.setTotalCount(tqr.query(sql,new ScalarHandler<Long>(),x).intValue());
        sql="select * from t_book where "+type+"=? order by orderBy limit ?,?";
        List<Book> list=tqr.query(sql,new BeanListHandler<Book>(Book.class),x,(pageNow-1)*pageCount,pageCount);
        bean.setList(list);
        bean.setPageCount(pageCount);
        return bean;
    }

    @Override
    public Book getBookByid(String bid) throws SQLException {

        return tqr.query("select * from t_book where bid=?",new BeanHandler<>(Book.class),bid);
    }

    @Override
    public PageBean<Book> searchList(String bname, int pageNow) throws SQLException {
        PageBean<Book>bean=new PageBean<>();
        bean.setPageNow(pageNow);
        //查询总记录数
        String sql="select count(*) from t_book where bname like?";
        bean.setTotalCount(tqr.query(sql,new ScalarHandler<Long>(),"%"+bname+"%").intValue());
        sql="select * from t_book where bname like ? order by orderBy limit ?,?";
        List<Book> list=tqr.query(sql,new BeanListHandler<Book>(Book.class),"%"+bname+"%",(pageNow-1)*pageCount,pageCount);
        bean.setList(list);
        bean.setPageCount(pageCount);
        return bean;
    }

    @Override
    public PageBean<Book> searchList(String bname, String author, String press, int pageNow) throws SQLException {
        List param =new ArrayList<>();
        PageBean<Book>bean=new PageBean<>();
        bean.setPageNow(pageNow);
        StringBuilder sql=new StringBuilder("select * from t_book where 1=1 ");
        StringBuilder sql2=new StringBuilder("select count(*) from t_book where 1=1 ");
        if (bname!=null && !bname.equals("")){
            sql.append(" and bname like ? ");
            sql2.append(" and bname like ? ");
            param.add("%"+bname+"%");
        }
        if (author!=null && !author.equals("")){
            sql.append(" and author like ? ");
            sql2.append(" and author like ? ");
            param.add("%"+author+"%");
        }
        if (press!=null && !press.equals("")){
            sql.append(" and press like ? ");
            sql2.append(" and press like ? ");
            param.add("%"+press+"%");
        }
        sql.append(" limit  ?,?");
        bean.setTotalCount(tqr.query(sql2.toString(),new ScalarHandler<Long>(),param.toArray()).intValue());
        param.add((pageNow-1)*pageCount);
        param.add(pageCount);
        List<Book> list = tqr.query(sql.toString(), param.toArray(), new BeanListHandler<>(Book.class));
        bean.setList(list);
        bean.setPageCount(pageCount);
        return bean;
    }


}
